package Orm.dbutils;

import Orm.jdbc.connectorPool.JDBCUtils;
import Orm.jdbc.ormdiy.City;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ResultSetHandlerStudy {

    public static void main(String[] args) throws SQLException {
        ScalarHandlerStudy();
    }

    /**
     * 自定义映射实现
     */
    public void impResultSetHandlerBySelf(){
        ResultSetHandler<City> resultSet = new ResultSetHandler<City>() {
            @Override
            public City handle(ResultSet resultSet) throws SQLException {

                return null;
            }
        };
    }

    //除了上面的自定义实现外，DbUtils也提供了一些实现供我们直接使用
    /**
     * ArrayHandler将结果集的第一行数据按照顺序存入数组中，数据中一个元素代表一列的一个值
     */
    public static void ArrayHandlerStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        QueryRunner runner = new QueryRunner(JDBCUtils.dataSource);
        Object[] city =  runner.query("select * from city limit 1", new ArrayHandler());
        for(Object o:city){
            System.out.print(o+"| ");
        }
    }

    /**
     * ArrayListHandler
     */
    public static void ArrayListHandlerStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        QueryRunner runner = new QueryRunner(JDBCUtils.dataSource);
        List<Object[]> cityList =  runner.query("select * from city limit 5", new ArrayListHandler());
        for(Object[] city:cityList){
            for(Object o:city){
                System.out.print(o+"| ");
            }
            System.out.println();
        }
    }

    /**
     * BeanHandler支持直接将查询到的数据赋值给实体的属性中，使用频率高
     * @throws SQLException
     */
    public static void BeanHandlerStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        QueryRunner runner = new QueryRunner(JDBCUtils.dataSource);
        City city =  runner.query("select * from city limit 1", new BeanHandler<>(City.class));
        System.out.println(city);
    }

    /**
     * BeanHandler支持直接将查询到的数据赋值给实体的属性中,并放入List中，使用频率高
     * @throws SQLException
     */
    public static void BeanListHandlerStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        QueryRunner runner = new QueryRunner(JDBCUtils.dataSource);
        List<City> cityList =  runner.query("select * from city limit 5", new BeanListHandler<>(City.class));
        for(City city:cityList){
            System.out.println(city);
        }
    }

    /**
     *将结果集中某一列的数据存放到List中,比较常用
     */
    public static void ColumnListHandlerStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        QueryRunner runner = new QueryRunner(JDBCUtils.dataSource);
        List<Object> cityList =  runner.query("select * from city limit 5", new ColumnListHandler<>("Name"));
        for(Object city:cityList){
            System.out.println(city);
        }
    }

    /**
     * 将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
     */
    public static void MapHandlerStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        QueryRunner runner = new QueryRunner(JDBCUtils.dataSource);
        Map<String,Object> city =  runner.query("select * from city limit 1", new MapHandler());
        for(Map.Entry<String,Object> entry:city.entrySet()){
            System.out.println(entry.getKey()+"="+entry.getValue());
        }
    }

    /**
     *将结果集中的每一行数据都封装到一个Map里，然后再将所有的Map存放到List中。
     */
    public static void MapListHandlerStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        QueryRunner runner = new QueryRunner(JDBCUtils.dataSource);
        List<Map<String,Object>> mapList = runner.query("select * from city limit 2", new MapListHandler());
        for(Map<String,Object> col:mapList){
            for(Map.Entry<String,Object> entry:col.entrySet()){
                System.out.print(entry.getKey()+"="+entry.getValue());
                System.out.print(",");
            }
            System.out.println();
        }
    }

    /**
     * 将结果集每一行数据保存到一个“小”map中,key为列名，value该列的值，再将所有“小”map对象保存到一个“大”map中 ， “大”map中的key为指定行，value为“小”map对象
     */
    public static void KeyedHandlerStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        QueryRunner runner = new QueryRunner(JDBCUtils.dataSource);
        Map<Integer,Map<String,Object>> mapList = runner.query("select * from city limit 2", new KeyedHandler<>());
        for(Map.Entry<Integer,Map<String,Object>> mapEntry:mapList.entrySet()){
            System.out.println(mapEntry.getKey()+": ");
            for(Map.Entry<String,Object> entry:mapEntry.getValue().entrySet()){
                System.out.print(entry.getKey()+"="+entry.getValue());
                System.out.print(",");
            }
            System.out.println();
        }
    }

    /**
     *通常用来保存只有一行一列的结果集。
     */
    public static void ScalarHandlerStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        QueryRunner runner = new QueryRunner(JDBCUtils.dataSource);
        String sql = "select * from city limit 1";
        Object o =runner.query("select * from city limit 1", new ScalarHandler<>("countryCode"));
        System.out.println(o);
    }



}
